package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db1 *sqlx.DB //sqlx更加强大
func initDB1() (err error) {
	dsn := "root:root@tcp(127.0.0.1:3306)/blog?charset=utf8mb4&parseTime=True"
	// 也可以使用MustConnect连接不成功就panic
	db1, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db1.SetMaxOpenConns(20)
	db1.SetMaxIdleConns(10)
	return
}

type Test1 struct {
	ID       int
	Name     string
	Password string
}

// 查询单条数据示例
func queryRowDemo() {
	sqlStr := "select * from test where id=?"
	var u Test1
	err := db1.Get(&u, sqlStr, 2)
	if err != nil {
		fmt.Printf("get failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Password)
}

// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select * from test where id > ?"
	var users []Test1
	err := db1.Select(&users, sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	fmt.Printf("users:%#v\n", users)
}

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into test(id,name, password) values (?,?,?)"
	ret, err := db1.Exec(sqlStr, 3, "沙河小王子", "19")
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
	sqlStr := "update test set name=? where id = ?"
	ret, err := db1.Exec(sqlStr, "ewewe", 2)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from test where id = ?"
	ret, err := db1.Exec(sqlStr, 2)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}
func main() {
	_ = initDB1()
	//queryRowDemo()
	//insertRowDemo()
	//queryMultiRowDemo()
	//updateRowDemo()
	deleteRowDemo()
	defer db1.Close()
}
